﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace MJCOMMEN_CS.DBUtility
{
    public class DBHelper
    {
        public DBHelper()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }
        static string connectString = System.Configuration.ConfigurationManager.AppSettings["DBString"];

        public static void ExcuteSql(string Sql)
        {
            SqlConnection conn = new SqlConnection(connectString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(Sql, conn);
            cmd.CommandTimeout = 90;
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public static void ExcuteSql(string Sql, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(connectString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(Sql, conn);
            cmd.CommandTimeout = 90;
            if (pars != null)
            {
                foreach (SqlParameter p in pars)
                {
                    cmd.Parameters.Add(p);
                }
            }
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            conn.Close();
        }
        public static DataTable QueryDt(string Sql, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(connectString);
            conn.Open();
            SqlDataAdapter cmd = new SqlDataAdapter(Sql, conn);
            cmd.SelectCommand.CommandTimeout = 90;
            if (pars != null)
            {
                foreach (SqlParameter p in pars)
                {
                    cmd.SelectCommand.Parameters.Add(p);
                }
            }
            DataSet ds = new DataSet();
            cmd.Fill(ds);
            cmd.SelectCommand.Parameters.Clear();
            conn.Close();
            return ds.Tables[0];
        }
        public static DataTable QueryPagedDt(string TableName, string FieldKey, int PageCurrent, int PageSize, string FieldShow, string FieldOrder, string Where, ref int RecordCount)
        {
            SqlConnection conn = new SqlConnection(connectString);
            conn.Open();
            SqlDataAdapter cmd = new SqlDataAdapter("sp_PageView", conn);
            cmd.SelectCommand.CommandType = CommandType.StoredProcedure;
            SqlParameter[] pars = new SqlParameter[] { 
                new SqlParameter("@tbname",TableName),
                new SqlParameter("@FieldKey",FieldKey),
                new SqlParameter("@PageCurrent",PageCurrent),
                new SqlParameter("@PageSize",PageSize),
                new SqlParameter("@FieldShow",FieldShow),
                new SqlParameter("@FieldOrder",FieldOrder),
                new SqlParameter("@Where",Where),
                new SqlParameter("@RecordCount",RecordCount)
            };
            pars[7].Direction = ParameterDirection.Output;
            foreach (SqlParameter p in pars)
                cmd.SelectCommand.Parameters.Add(p);
            DataSet ds = new DataSet();
            cmd.Fill(ds);
            cmd.SelectCommand.Parameters.Clear();
            RecordCount = (int)pars[7].Value;
            conn.Close();

            return ds.Tables[0];
        }

        public static DataTable QueryDt(string Sql)
        {
            SqlConnection conn = new SqlConnection(connectString);
  
            conn.Open();
            SqlDataAdapter cmd = new SqlDataAdapter(Sql, conn);
            cmd.SelectCommand.CommandTimeout = 90;
            DataSet ds = new DataSet();
            cmd.Fill(ds);
            conn.Close();
            return ds.Tables[0];
        }
        public static DataSet QueryDs(string Sql)
        {
            SqlConnection conn = new SqlConnection(connectString);
            conn.Open();
            SqlDataAdapter cmd = new SqlDataAdapter(Sql, conn);
            cmd.SelectCommand.CommandTimeout = 90;
            DataSet ds = new DataSet();
            cmd.Fill(ds);
            conn.Close();
            return ds;
        }
        #region ExecSqlTransaction

        public static bool ExecSqlTransaction(string strSql, SqlParameter[] sqlParameter)
        {
            ArrayList sqlParameterList = new ArrayList();
            sqlParameterList.Add(sqlParameter);
            return ExecSqlTransaction(new string[] { strSql }, sqlParameterList);
        }

        public static bool ExecSqlTransaction(string strSql, ArrayList sqlParameterList)
        {
            return ExecSqlTransaction(new string[] { strSql }, sqlParameterList);
        }

        public static bool ExecSqlTransaction(string[] strSql, ArrayList sqlParameterList)
        {
            bool returnState = false;

            SqlConnection conn = new SqlConnection(connectString);
            conn.Open();

            SqlTransaction sqlTransaction = conn.BeginTransaction();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandTimeout = 90;
            sqlCommand.Connection = conn;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.Transaction = sqlTransaction;

            try
            {
                for (int i = 0; i < sqlParameterList.Count; i++)
                {
                    if (strSql.Length == 1)
                    {
                        sqlCommand.CommandText = strSql[0];
                    }
                    else
                    {
                        sqlCommand.CommandText = strSql[i];
                    }

                    sqlCommand.Parameters.Clear();
                    SqlParameter[] prams = (SqlParameter[])sqlParameterList[i];
                    foreach (SqlParameter pram in prams)
                    {
                        sqlCommand.Parameters.Add(pram);
                    }
                    sqlCommand.ExecuteNonQuery();
                }
                sqlTransaction.Commit();
                returnState = true;
            }
            catch
            {
                sqlTransaction.Rollback();
                throw;

            }
            finally
            {
                conn.Close();
            }

            return returnState;
        }
        #endregion
    }
}
